/*
* MssqlAdapter.java v_0.01 2010/9/06
*
* Copyright (c) 2010 Jong Sung Lee.
* Medical Infomatics Laboratory (718 Ho), Basic Research Building, 
* College of Medicine Seoul National Univ, Yeongeon-dong, Jongno-gu, 
* Seoul, Korea (Seoul 110-799 Korea)
* All rights reserved.
*
* This software is the confidential and proprietary information of Medical Infomatics 
* Laboratory ("Confidential Information"). You shall not disclose such Confidential
* Information and shall use it only in accordance with the terms of the license
* agreement you entered into with Medinfo.
*/

package kr.ac.snu.medinfo.edc;

//import

/**
* MssqlAdapter
* 설명 : Mssql Server 와 연동하기 위한 클래스
* MSSQL에서 사용되는 SQL 쿼리를 작성해두고 이 정보를 JDBCAdapter에서 가져다 사용함.
*
* @version 0.01 06 Sep 2010
* @author Jong Sung Lee
*/

public class MssqlAdapter extends JDBCAdapter {
	public MssqlAdapter(String uid, String pwd, String ip, int port) {
		connectionString = "jdbc:sqlserver://" + ip + ":" + port;
		DBUserId = uid;
		DBPassword = pwd;
	}

	@Override
	protected void loadDriver() throws ClassNotFoundException {
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	}
	
	//Researches Table SQL Query
	@Override
	protected String getSQLforReadResearches(String ResearchId) {
		
		String query = "SELECT ResearchId, ResearchName, StartingTime, ExpectedFinishTime " +
		   			   "FROM Researches " +
		   			   "WHERE ResearchId = '" + ResearchId + "'";

		return query;
	}
	
	@Override
	public String getSQLforInsertResearches(String ResearchId, String ResearchName, 
			String StartingTime, String ExpectedFinishTime){
		
		String query = "INSERT INTO Researches" +
							"(ResearchId, ResearchName,StartingTime, ExpectedFinishTime) " +
					   "VALUES('" + ResearchId + "','"+ ResearchName + 
					   		"','" + StartingTime + "','" + ExpectedFinishTime + "')";
		return query;
	}

	@Override
	protected String getSQLforDeleteResearches(String ResearchId) {
		String query = "DELETE FROM Researches " +
		   			   "WHERE ResearchId = '" + ResearchId + "'" ;

		return query;
	}

	@Override
	protected String getSQLforUpdateResearches(String ResearchId,String ResearchName,
			String StartingTime, String ExpectedFinishTime) {
		
		String query = "UPDATE Researches " +
					   "SET ResearchName='" +ResearchName+ "', " +
					   	   "StartingTime='" +StartingTime+ "', " +
						   "ExpectedFinishTime='" +ExpectedFinishTime+ "' " +
					   "WHERE ResearchId='" +ResearchId+ "'";
		
		return query;
	}
	
	//Tests Table SQL Query
	@Override
	protected String getSQLforReadTests(String TestId) {
		String query = "SELECT TestId, CRF_Name, CRF_TestCode, EMR_Name, EMR_TestCode " +
		   "FROM Tests " +
		   "WHERE TestId = '" + TestId + "'";

		return query;
	}
	
	@Override
	protected String getSQLforInsertTests(String TestId, String CRF_Name,
			String CRF_TestCode, String EMR_Name, String EMR_TestCode) {
		String query = "INSERT INTO Tests" +
							"(TestId, CRF_Name,CRF_TestCode, EMR_Name, EMR_TestCode) " +
						"VALUES('" +TestId+ "','"+CRF_Name+ "','" +CRF_TestCode+ 
						    "','" +EMR_Name+ "','" +CRF_TestCode +"')";
		return query;
	}
	
	@Override
	protected String getSQLforDeleteTests(String TestId) {
		String query = "DELETE FROM Tests " +
		   "WHERE TestId = '" + TestId + "'" ;

		return query;
	}

	@Override
	protected String getSQLforUpdateTests(String TestId, String CRF_Name,
			String CRF_TestCode, String EMR_Name, String EMR_TestCode) {
		String query = "UPDATE Tests " +
		   "SET CRF_Name='" +CRF_Name+ "', " +
		   	   "CRF_TestCode='" +CRF_TestCode+ "', " +
			   "EMR_Name='" +EMR_Name+ "', " +
			   "EMR_TestCode='" +EMR_TestCode+ "' " +
		   "WHERE TestId='" +TestId+ "'";

		return query;
	}

	@Override
	protected String getSQLforInsertSubjects(String SSN, String Name,
			String CRF_SubjectID, String EMR_PID, String EnrolledTime) {
		
		String query = "INSERT INTO Subjects" +
							"(SSN, Name,CRF_SubjectID, EMR_PID, EnrolledTime) " +
					   "VALUES('" +SSN+ "','"+Name+ "','" +CRF_SubjectID+ 
					   		"','" +EMR_PID+ "','" +EnrolledTime +"')";
		return query;
	}

	@Override
	protected String getSQLforUpdateSubjects(String SSN, String Name,
			String CRF_SubjectID, String EMR_PID, String EnrolledTime) {
		String query = "UPDATE Subjects " +
		   "SET Name='" +Name+ "', " +
		   	   "CRF_SubjectID='" +CRF_SubjectID+ "', " +
			   "EMR_PID='" +EMR_PID+ "', " +
			   "EnrolledTime='" +EnrolledTime+ "' " +
		   "WHERE SSN='" +SSN+ "'";

		return query;
	}

	@Override
	protected String getSQLforDeleteSubjects(String SSN) {
		String query = "DELETE FROM Subjects " +
		   "WHERE SSN = '" + SSN + "'" ;

		return query;
	}

	@Override
	protected String getSQLforReadSubjects(String SSN) {
		String query = "SELECT SSN, Name, CRF_SubjectID, EMR_PID, EnrolledTime " +
		   "FROM Subjects " +
		   "WHERE SSN = '" + SSN + "'";

		return query;
	}

	@Override
	protected String getSQLforInsertUsers(User user) {
		
		String query = "INSERT INTO Users" +
			"(Name,UserId, Password, SSN, Email, PhoneNumber, " +
			"MobileNumber, NumberOfMessage) " +
			"VALUES('" +user.getName()+ "','"+user.getUserId()+ "','" +user.getPassword()+ 
			"','" +user.getSSN()+ "','" +user.getEmail() +"','" +user.getPhoneNumber() +
			"','" +user.getMobileNumber() +"'," +user.getNumberOfMessage() +")";
		return query;
	}

	@Override
	protected String getSQLforUpdateUsers(User user) {
		
		String query = "UPDATE Users " +
		   "SET Password='" +user.getPassword()+ "', " +
		   	   "Email='" +user.getEmail()+ "', " +
			   "PhoneNumber='" +user.getPhoneNumber()+ "', " +
			   "MobileNumber='" +user.getMobileNumber()+ "', " +
			   "NumberOfMessage=" +user.getNumberOfMessage() + " " +
		   "WHERE UserId='" +user.getUserId()+ "'";

		return query;
	}

	@Override
	protected String getSQLforDeleteUsers(User user) {
		String query = "DELETE FROM Users " +
		   "WHERE UserId = '" + user.getUserId() + 
		   "' and Password = '" + user.getPassword() + 
		   "' and SSN ="+user.getSSN();

		return query;
	}

	@Override
	protected String getSQLforReadUsers(User user) {
		String query = "SELECT Name, UserId, Password, SSN, Email, PhoneNumber," +
			"MobileNumber, NumberOfMessage FROM Users " +
		   "WHERE UserId = '" + user.getUserId() + "'";
		return query;
	}

	//-----------------------------------------------------------------
	// 아래부분 미작성됨.
	@Override
	protected String getSQLforInsertApprovals(Approval approval) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected String getSQLforUpdateApprovals(Approval approval) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected String getSQLforDeleteApprovals(Approval approval) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected String getSQLforReadApprovals(Approval approval) {
		// TODO Auto-generated method stub
		return null;
	}
}
